import matplotlib.pyplot as plt

import pandas as pd
import mysql.connector

db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'ForeverM59',
    'database': 'datacopilot'
}

# 连接到MySQL数据库
conn = mysql.connector.connect(**db_config)

if conn.is_connected():
    cursor = conn.cursor()

    # 执行SQL查询
    query = """
            SELECT grade, COUNT(*) as count
            FROM student_info
            GROUP BY grade;
            """
    cursor.execute(query)

    # 获取查询结果
    results = cursor.fetchall()

    df = pd.DataFrame(results, columns=['grade', 'count'])

    cursor.close()
    conn.close()

    # 绘制柱状图
plt.figure(figsize=(10, 6))  # 设置图形大小
plt.bar(df['grade'], df['count'], color=['blue', 'green', 'red', 'purple'])  # 假设有四种成绩等级
plt.xlabel('Grade Level')
plt.ylabel('Number of Students')
plt.title('Number of Students by Grade Level')
plt.xticks(rotation=45)  # 旋转x轴标签以便阅读
chart_path = plt.savefig('student_grade_distribution.png')
plt.close()
